﻿use housing
/*创建合并表*/
if not exists(select name from sysobjects where type='u' and  name='租金减免临时数据'  ) 
begin 
	--drop table [租金减免临时数据]
	CREATE TABLE [租金减免临时数据](
		[编号] [nvarchar](255) NULL,
		[承租地址] [nvarchar](255) NULL,
		[承租人] [nvarchar](255) NULL,
		[计租面积] [float] NULL,
		[核定月租金] [float] NULL,
		[月减免金额] [float] NULL,
		[截止日期] [nvarchar](255) NULL,
		[年] int,
		[月] int 
	) 
	
end 



/*存储过程-合并excel表*/
if exists(select 1 from sysobjects where xtype='P' and name='InfluxExlData_') drop procedure InfluxExlData_
go
Create proc InfluxExlData_(@fromMonth varchar(2) ,@toMonth varchar(2),@year varchar(4))
as 
begin 
	declare @sql varchar(max),@sqlwehre varchar(max)
	set @sql=''
	if @fromMonth>@toMonth return 
	while @fromMonth<@toMonth+1
	begin 
		set @sql='insert into [租金减免临时数据] select [编号],承租地址,承租人,计租面积,核定月租金,月减免金额,截止日期,'+@year+','+@fromMonth +' from ['''+@year+'#'+@fromMonth+'_$'']    '
		exec(@sql)
		set @fromMonth=@fromMonth+1		
	end 	 
	
end 
GO 

/*存储过程-临时表去重复*/
if exists(select 1 from sysobjects where xtype='P' and name='GetTempDataDistinct_') drop procedure GetTempDataDistinct_
go
Create proc GetTempDataDistinct_
as 
begin  
	select distinct *  into #temptable from [租金减免临时数据] where 编号 is not null
	delete   from  [租金减免临时数据]
	insert into  [租金减免临时数据] select * from #temptable
	drop table #temptable
end 
GO 



/*存储过程-导入到[ReduceRecord]函数*/
if exists(select 1 from sysobjects where xtype='P' and name='ImportData_') drop proc ImportData_
go
Create proc ImportData_(@year int,@month int)
as 
begin 

	insert into ReduceRecord
	SELECT 
	  [编号] as [adj_longid]
	  ,1 as [adj_checked]
	  ,[计租面积] as [adj_nowarea]
	  ,核定月租金 as [adj_nowmoney]
	  ,月减免金额 as [adj_nowreduce]
	  ,dbo.getMoneyChange_([编号],[月减免金额],[年],[月])   as  [adj_reducechange]  --待改
	  ,0 as [adj_iscontinue]
	  ,dbo.fixYearMonth([年],[月]) [adj_starttime]
	  ,dbo.DateTimeToInt([截止日期]) as [adj_stoptime]
	  ,'' as [adj_remark]
	  ,dbo.fixYearMonth([年],[月]) as [adj_date]
	  ,承租人 as [adj_renter]
	  ,[承租地址] as [adj_rentaddress]
	  ,dbo.getNoID([编号]) as [adj_noid]
	  ,'' as [adj_reason]
  FROM [租金减免临时数据] where [年]=@year and  [月]=@month  and dbo.getMoneyChange_([编号],[月减免金额],[年],[月])<>0 and [编号] is not null
End
GO

/*函数-某个人的补发金额变化*/
if exists(select * from sysobjects where  name='getMoneyChange_' and xtype='FN' ) drop function getMoneyChange_
go
Create function getMoneyChange_(@ID nvarchar(255),@nowreduce float,@year int,@month int)
returns float
as 
begin 
	declare @adj_moneychange float
	declare @adj_id int	
	declare @thisdate int
	set @thisdate=dbo.fixYearMonth(@year,@month)
	select top 1  @adj_moneychange=(@nowreduce-[adj_nowreduce]) from [ReduceRecord] 
	where [adj_longid]=@ID and  adj_date<@thisdate order by adj_date desc
	--如果前面的月份数据没有，上面@adj_moneychange为null 
	if(@adj_moneychange is null)
		begin 
			set @adj_moneychange=@nowreduce
		end 
	return @adj_moneychange
end
GO

/*函数-得到终止时间*/
if exists(select * from sysobjects where  name='getStopTime_' and xtype='FN' ) drop function getStopTime_
go
Create function getStopTime_(@id varchar(150))
returns int
as 
begin 
	declare @stoptime varchar(100),@year varchar(4),@month varchar(2)
	select @stoptime=[享受终止时间] from [标准享受类别表] where [编号]=@id
	set @year='20'+right(@stoptime,2)
	set @month=dbo.EnMonthToNum(substring(@stoptime,4,3))
	return cast(@year+@month as int)
end
----SELECT dbo.getStopTime('A05000003')
GO
/*函数-合并时间*/
if exists(select * from sysobjects where  name='fixYearMonth' and xtype='FN' ) drop function fixYearMonth
go
Create function fixYearMonth(@year int,@month int)
returns int
as 
begin 
	declare @str varchar(6)
	declare @monthstr varchar(2),@yearstr varchar(4)
	set @monthstr=cast(@month as varchar(2))
	set @yearstr=cast(@year as varchar(4))
	if @month<10  
	begin 
		set @monthstr='0'+@monthstr
	end 
	set @str=@yearstr+@monthstr
	return cast(@str as int);
end
GO

/*函数-得到临时编号*/
if exists(select * from sysobjects where  name='getNoID' and xtype='FN' ) drop function getNoID
go
Create function getNoID(@id varchar(150))
returns int
as 
begin 
	declare @noid int;
	select @noid=apply_noid from apply where apply_longid=@id
	return @noid
end 
----SELECT dbo.getNoID(1)
GO
/*函数-月份转换*/
if exists(select * from sysobjects where  name='EnMonthToNum' and xtype='FN' ) drop function EnMonthToNum
go
Create function EnMonthToNum(@m varchar(10))
returns varchar(10)
as 
begin 
	declare @mstr varchar(10)
	select @mstr=Case LOWER(@m)
					when 'jan' then  '01'
					when 'feb' then  '02'
					when 'mar' then  '03'
					when 'apr' then  '04'
					when 'may' then  '05'
					when 'jan' then  '06'
					when 'jul' then  '07'
					when 'aug' then  '08'
					when 'sep' then  '09'
					when 'oct' then  '10'
					when 'nov' then  '11'
					when 'dec' then  '12'					
				 end 
	return @mstr
end 
go
--select dbo.EnMonthToNum('APR')


/*函数-得到临时编号*/
if exists(select * from sysobjects where  name='DateTimeToInt' and xtype='FN' ) drop function DateTimeToInt
go
Create function DateTimeToInt(@date datetime)
returns int
as 
begin 
	return dbo.fixYearMonth(year(@date),month(@date))
end 
go
----SELECT dbo.getNoID(1)